package com.xiaoruyuan.firstday.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class JdbcUtils {

    public static enum DatabaseType {

        LOCAL_MYSQL("mysql"),
        REMOTE_MySQL("remote-mysql");

        private String name;

        private DatabaseType(String name) {
            this.name = name;
        }

        public String getName() {
            return name;
        }
    }

    ;

    private static Properties props;

    static {
        try {
            InputStream in = Thread.currentThread()
                    .getContextClassLoader()
                    .getResourceAsStream("jdbc.properties");
            props = new Properties();
            props.load(in);
        } catch (IOException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * 获取数据库连接
     *
     * @param type 要操作的数据库
     * @return
     * @throws Exception
     */
    public static Connection getConnection(DatabaseType type) throws Exception {
        Class.forName(props.getProperty(type.getName() + ".driver"));
        String jdbcUrl = props.getProperty(type.getName() + ".jdbcUrl");
        String user = props.getProperty(type.getName() + ".user");
        String password = props.getProperty(type.getName() + ".password");
        return DriverManager.getConnection(jdbcUrl, user, password);
    }

    /**
     * 将查询的数据复制到另一个数据库的表中，要求两张表的字段名，字段类型完全相同。
     *
     * @param src           要查询的数据库
     * @param sql           要查询的 SQL
     * @param dest          目标数据库
     * @param destTableName 目标表名称
     * @return
     */
    public static int[] copy(DatabaseType src, String sql, DatabaseType dest, String destTableName) {
        int count[] = new int[0];
        List<Map<String, Object>> query = JdbcUtils.query(DatabaseType.REMOTE_MySQL, sql);
        String insertSql = "insert into %s(%s) values(%s)";
        StringBuilder key = new StringBuilder();
        StringBuilder value = new StringBuilder();
        List<String> columns = new ArrayList<>();
        List<List<Object>> params = new ArrayList<>();
        if (query.size() > 0) {
            for (String column : query.get(0).keySet()) {
                key.append("`"+column+"`").append(",");
                value.append("?,");
                columns.add(column);
            }
            insertSql = String.format(insertSql,
                    destTableName,
                    key.substring(0, key.length() - 1).toString(),
                    value.substring(0, value.length() - 1).toString());

            for (Map<String, Object> map : query) {
                List<Object> param = new ArrayList<>();
                for (String column : columns) {
                    param.add(map.get(column));
                }
                params.add(param);
            }
            count = JdbcUtils.executeBatch(DatabaseType.LOCAL_MYSQL, insertSql, params);
        }
        return count;
    }

    /**
     * 批量执行多条 SQL 语句
     *
     * @param type 要执行的数据库
     * @param sqls SQL 语句
     * @return
     */
    public static int[] executeBatch(DatabaseType type, List<String> sqls) {
        int count[] = new int[0];
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection(type);
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            for (String sql : sqls) {
                stmt.addBatch(sql);
            }
            count = stmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(autoCommit);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeStatement(stmt);
            closeConnection(conn);
        }
        return count;
    }

    /**
     * 批量执行一个 SQL 语句，可以传不同的参数
     *
     * @param type   要执行的数据库
     * @param sql    SQL 语句
     * @param params 参数列表
     * @return
     */
    public static int[] executeBatch(DatabaseType type, String sql, List<List<Object>> params) {
        int count[] = new int[0];
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getConnection(type);
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);

            pst = conn.prepareStatement(sql);

            for (List<Object> list : params) {

                for (int i = 0; i < list.size(); i++) {


                    pst.setObject(i + 1, list.get(i));
                }
                pst.addBatch();
            }

            count = pst.executeBatch();

            conn.commit();
            conn.setAutoCommit(autoCommit);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            System.out.println(pst);
            closeStatement(pst);
            closeConnection(conn);
        }
        return count;
    }

    /**
     * 执行单条 SQL 语句
     *
     * @param type 要执行的数据库
     * @param sql  SQL 语句
     * @param args 参数列表
     * @return
     */
    public static int execute(DatabaseType type, String sql, Object... args) {
        int count = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getConnection(type);
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
            count = pst.executeUpdate();
            conn.commit();
            conn.setAutoCommit(autoCommit);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeStatement(pst);
            closeConnection(conn);
        }
        return count;
    }

    /**
     * 查询数据并封装到 List 集合中
     *
     * @param type 要查询的数据库
     * @param sql  SQL 语句
     * @param args 参数列表
     * @return List<Map < 字段名, 值>>
     */
    public static List<Map<String, Object>> query(DatabaseType type, String sql, Object... args) {
        List<Map<String, Object>> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = getConnection(type);
            pst = conn.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    pst.setObject(i + 1, args[i]);
                }
            }

            rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();
            List<String> columns = new ArrayList<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                columns.add(rsmd.getColumnName(i)); // 字段名
            }
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                for (String column : columns) {
                    map.put(column, rs.getObject(column));
                }
                result.add(map);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeStatement(pst);
            closeConnection(conn);
        }
        return result;
    }

    /**
     * 关闭 Statement
     *
     * @param stmt
     * @return
     */
    public static boolean closeStatement(Statement stmt) {
        boolean flag = true;
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }

    /**
     * 关闭 ResultSet
     *
     * @param rs
     * @return
     */
    public static boolean closeResultSet(ResultSet rs) {
        boolean flag = true;
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }

    /**
     * 关闭 Connection
     *
     * @param conn
     * @return
     */
    public static boolean closeConnection(Connection conn) {
        boolean flag = true;
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }

    public static void main(String[] args) throws Exception {

        // 将 oracle 中的 scott.emp 表中的数据插入到 mysql 的 emp 表中
        long start = System.currentTimeMillis();
        String sql = "select table_name from information_schema.tables where table_schema='wm_ruyuan2020';";
        List<Map<String, Object>> query = JdbcUtils.query(DatabaseType.REMOTE_MySQL, sql, null);
        int countNumber = 0;
        for (Map<String, Object> tableMap : query) {
            for (Object value : tableMap.values()) {
                String tableName = value.toString();
                String tableSelectSql = "select * from " + tableName;
                int[] count = JdbcUtils.copy(DatabaseType.REMOTE_MySQL, tableSelectSql, DatabaseType.LOCAL_MYSQL, tableName);
                countNumber += count.length;
            }

        }
        long end = System.currentTimeMillis();
        System.out.println(String.format("用时：%s, countNumber = %s", end - start, countNumber));

    }

}
